﻿CREATE PROCEDURE [dbo].[usp_select_Alerts]	
AS
	SET NOCOUNT ON;

SELECT     
	[AlertID], 
	[Name], 
	[ConditionsString], 
	[InsertedBy],
	[InsertedDate],
	(SELECT COUNT(AlertID) FROM dbo.AlertSubscription s WHERE AlertID = a.AlertID AND s.Subscriber = a.InsertedBy) AS OwnerSubscriberCount,
	(SELECT COUNT(AlertID) FROM dbo.AlertSubscription s WHERE AlertID = a.AlertID AND s.Subscriber != a.InsertedBy) AS OtherSubscriberCount,
	(SELECT COUNT(AlertID) FROM dbo.AlertSubscription s WHERE AlertID = a.AlertID) AS TotalSubscriberCount
FROM
	dbo.Alert a
